package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import connection.ConnectionManager;
import domain.Movie;

public class JDBCMovieDAO implements IMovieDAO {

	Connection conn;

	public List<Movie> getAllMovies() {
		List<Movie> Movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid");
			Movie movie = null;
			while (rs.next()) {
				movie = new Movie(rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5), rs.getString(6), rs.getString(7),rs.getString(8),rs.getString(9), rs.getInt(10));
				Movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Movies;
	}

	
	public List<Movie> getAllMoviesStartingWithLetter(char letter) {
		List<Movie> Movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE titulo LIKE '"+letter+"%'");
			Movie movie = null;
			while (rs.next()) {
				movie = new Movie(rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5), rs.getString(6), rs.getString(7),rs.getString(8),rs.getString(9), rs.getInt(10));
				Movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Movies;
	}
	
	public List<Movie> getAllMoviesByGenre(String genre){
	List<Movie> movies = new LinkedList<Movie>();
	try {
		conn = ConnectionManager.getInstance().openConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT pid,titulo,anno,duracion,descripcion,pais,genero,caratula,trailer, capturas,video_vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE v.genero LIKE '%" + genre + "%'");		
		Movie movie = null;
		while(rs.next()){
			movie = new Movie(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11));
			movies.add(movie);
		}
		rs.close();
		stmt.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return movies;
}
	public Movie getMoviesByIdVideo(Integer id) {
		Movie movie = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT trailer,capturas,titulo,anno,duracion,descripcion,pais,genero,caratula,vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE v.vid='"+id+"'");
			if(rs.next())
			{
				movie = new Movie(rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5), rs.getString(6), rs.getString(7),rs.getString(8),rs.getString(9), rs.getInt(10));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movie;
	}


	@Override
	public List<Movie> getAllMoviesByTitleYearGenre(String title, Integer year,
			String genre) {
		List<Movie> movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT pid,titulo,anno,duracion,descripcion,pais,genero,caratula,trailer, capturas,video_vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE titulo LIKE '%"+title+"%' AND anno='"+year+"' AND v.genero LIKE '%" + genre + "%'");		
			Movie movie = null;
			while(rs.next()){
				movie = new Movie(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11));
				movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movies;
	}


	@Override
	public List<Movie> getAllMoviesByTitleYear(String title, Integer year) {
		List<Movie> movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT pid,titulo,anno,duracion,descripcion,pais,genero,caratula,trailer, capturas,video_vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE titulo LIKE '%"+title+"%' AND anno='"+year+"'");		
			Movie movie = null;
			while(rs.next()){
				movie = new Movie(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11));
				movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movies;
	}


	@Override
	public List<Movie> getAllMoviesByTitleGenre(String title, String genre) {
		List<Movie> movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT pid,titulo,anno,duracion,descripcion,pais,genero,caratula,trailer, capturas,video_vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE titulo LIKE '%"+title+"%' AND v.genero LIKE '%" + genre + "%'");		
			Movie movie = null;
			while(rs.next()){
				movie = new Movie(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11));
				movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movies;
	}


	@Override
	public List<Movie> getAllMoviesByTitle(String title) {
		List<Movie> movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT pid,titulo,anno,duracion,descripcion,pais,genero,caratula,trailer, capturas,video_vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE titulo LIKE '%"+title+"%'");		
			Movie movie = null;
			while(rs.next()){
				movie = new Movie(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11));
				movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movies;
	}


	@Override
	public List<Movie> getAllMoviesByYearGenre(Integer year, String genre) {
		List<Movie> movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT pid,titulo,anno,duracion,descripcion,pais,genero,caratula,trailer, capturas,video_vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE anno='"+year+"' AND v.genero LIKE '%" + genre + "%'");		
			Movie movie = null;
			while(rs.next()){
				movie = new Movie(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11));
				movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movies;
	}


	@Override
	public List<Movie> getAllMoviesByYear(Integer year) {
		List<Movie> movies = new LinkedList<Movie>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT pid,titulo,anno,duracion,descripcion,pais,genero,caratula,trailer, capturas,video_vid FROM video v INNER JOIN peliculas p ON v.vid=p.video_vid WHERE anno='"+year+"'");		
			Movie movie = null;
			while(rs.next()){
				movie = new Movie(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11));
				movies.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movies;
	}

	
}
